package top.wanzc.java.basic.demo.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;

public class TransactionTest {
	
	public static void main(String[] args) {
        Connection con = null;
        Savepoint savepoint = null;
        try {
            con = DBConnection.getConnection();
            // set auto commit to false
            con.setAutoCommit(false);
			EmployeeJDBCInsertExample.insertEmployeeData(con, 1, "Pankaj");
			EmployeeJDBCInsertExample.insertAddressData(con, 1, "Albany Dr", "San Jose", "USA");
			// if code reached here, means main work is done successfully
            savepoint = con.setSavepoint("EmployeeSavePoint");
            EmployeeJDBCInsertExample.insertLogData(con, 2);
            // now commit transaction
            con.commit();
        }
        catch (SQLException e) {
            e.printStackTrace();
            try {
                if (savepoint == null) {
                    // SQLException occurred in saving into Employee or Address tables
                    con.rollback();
                    System.out.println("JDBC Transaction rolled back successfully");
                } else {
                    // exception occurred in inserting into Logs table
                    // we can ignore it by rollback to the savepoint
                    con.rollback(savepoint);
                    // lets commit now
                    con.commit();
                }
            }
            catch (SQLException e1) {
                System.out.println("SQLException in rollback" + e.getMessage());
            }
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (con != null)
                    con.close();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}


class DBConnection {
    public final static String DB_DRIVER_CLASS = "com.mysql.jdbc.Driver";
    public final static String DB_URL = "jdbc:mysql://localhost:3306/UserDB";
    public final static String DB_USERNAME = "pankaj";
    public final static String DB_PASSWORD = "pankaj123";
 
    public static Connection getConnection() throws ClassNotFoundException,
            SQLException {
        Connection con = null;
        Class.forName(DB_DRIVER_CLASS);
        con = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        System.out.println("DB Connection created successfully");
        return con;
    }
}

class EmployeeJDBCInsertExample {
	
    public static final String INSERT_EMPLOYEE_QUERY = "insert into Employee (empId, name) values (?,?)";
    public static final String INSERT_ADDRESS_QUERY = "insert into Address (empId, address, city, country) values (?,?,?,?)";
    public static final String INSERT_LOGS_QUERY = "insert into Logs (message) values (?)";
 
    public static void main(String[] args) {
        Connection con = null;
        try {
            con = DBConnection.getConnection();
            insertEmployeeData(con, 1, "Pankaj");
            insertAddressData(con, 1, "Albany Dr", "San Jose", "USA");
        }
        catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (con != null)
                    con.close();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
    public static void insertAddressData(Connection con,
                                         int id,
                                         String address,
                                         String city,
                                         String country) throws SQLException {
        PreparedStatement stmt = con.prepareStatement(INSERT_ADDRESS_QUERY);
        stmt.setInt(1, id);
        stmt.setString(2, address);
        stmt.setString(3, city);
        stmt.setString(4, country);
        stmt.executeUpdate();
        System.out.println("Address Data inserted successfully for ID=" + id);
        stmt.close();
    }
 
    public static void insertEmployeeData(Connection con, int id, String name)
            throws SQLException {
        PreparedStatement stmt = con.prepareStatement(INSERT_EMPLOYEE_QUERY);
        stmt.setInt(1, id);
        stmt.setString(2, name);
        stmt.executeUpdate();
        System.out.println("Employee Data inserted successfully for ID=" + id);
        stmt.close();
    }
    
    public static void insertLogData(Connection con, int i)
            throws SQLException {
        PreparedStatement stmt = con.prepareStatement(INSERT_LOGS_QUERY);
        // message is very long, will throw SQLException
        stmt.setString(1, "Employee information saved successfully for ID" + i);
        stmt.executeUpdate();
        System.out.println("Logs Data inserted successfully for ID=" + i);
        stmt.close();
    }
}

/*CREATE TABLE `Employee` (
 `empId` int(11) unsigned NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`empId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		 
CREATE TABLE `Address` (
  `empId` int(11) unsigned NOT NULL,
  `address` varchar(20) DEFAULT NULL,
  `city` varchar(5) DEFAULT NULL,
  `country` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`empId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;*/

/*CREATE TABLE `Logs` (
`id` int(3) unsigned NOT NULL AUTO_INCREMENT,
`message` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;*/
